package com.emmmya.utils.poidefault;

import com.emmmya.pojo.excel.MergedResult;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.nio.charset.StandardCharsets;
import java.util.Iterator;
import java.util.List;

/**
 * @author Harin
 * 操作poi的excel常用方法
 */
public class ExcelDefaultUtils {


    /**
     * 合并单元格
     * @param sheet
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     */
    public static void mergedRegion(Sheet sheet,Integer firstRow,Integer lastRow,
                                              Integer firstCol,Integer lastCol) {
        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(region);
    }

    /**
     * 判断是否为合并单元格
     * @param sheet     sheet页
     * @param row       行index
     * @param column    列index
     * @return
     */
    public static MergedResult isMergedRegion(Sheet sheet, int row , int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumnIndex = range.getFirstColumn();
            int lastColumnIndex = range.getLastColumn();
            int firstRowIndex = range.getFirstRow();
            int lastRowIndex = range.getLastRow();
            if (row >= firstRowIndex&& row <= lastRowIndex) {
                if (column >= firstColumnIndex && column <= lastColumnIndex) {
                    return new MergedResult(true, firstRowIndex, lastRowIndex, firstColumnIndex, lastColumnIndex );
                }
            }
        }
        return new MergedResult(false, 0, 0, 0, 0);
    }


    /**
     * @param sheet sheet
     * @param startRow 插⼊⾏的⾏标,即在哪⼀⾏下插⼊
     * @param rows 插⼊多少⾏
     * @param styleRow 是否创建真实的行
     */
    public static void insertAndCreateRow(Sheet sheet,int startRow, int rows,Row styleRow){
        insertRow(sheet,startRow, rows,false);
        for (int i = 0; i < rows; i++) {
            Row row = sheet.getRow(startRow + i);
            if(row == null) {
                row = sheet.createRow(startRow + i);
            }
            copyRow(styleRow,row);
        }
    }



    /**
    * @param sheet sheet
    * @param startRow 插⼊⾏的⾏标,即在哪⼀⾏下插⼊
    * @param rows 插⼊多少⾏
    * @param createRowFlag 是否创建真实的行
    */
    public static void insertRow(Sheet sheet,int startRow, int rows,Boolean createRowFlag) {


        //先获取原始的合并单元格address集合
        List<CellRangeAddress> originMerged = sheet.getMergedRegions();

        for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
            CellRangeAddress region = sheet.getMergedRegion(i);
            //判断移动的行数后重新拆分
            if (region.getFirstRow() > startRow) {
                sheet.removeMergedRegion(i);
            }
        }
        int lastRowNum = sheet.getLastRowNum();
        //如果开始行大于了
        if(startRow > lastRowNum){
            for (int j = lastRowNum+1; j <= startRow; j++) {
                sheet.createRow(j);
            }
        }


        sheet.shiftRows(startRow,sheet.getLastRowNum() ,rows,true,false);

        for(CellRangeAddress cellRangeAddress : originMerged) {
            //这里的8是插入行的index，表示这行之后才重新合并
            if(cellRangeAddress.getFirstRow() > startRow) {
                //你插入了几行就加几，我这里插入了一行，加1
                int firstRow = cellRangeAddress.getFirstRow() + rows;
                CellRangeAddress newCellRangeAddress = new CellRangeAddress(firstRow, (firstRow + (cellRangeAddress
                        .getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(),
                        cellRangeAddress.getLastColumn());
                sheet.addMergedRegion(newCellRangeAddress);
            }
        }

        //创建行，如果传入为false，则不创建行，看实际使用场景。
        if(createRowFlag){
            for (int j = 0; j < rows; j++) {
                Row row = sheet.getRow(startRow + j);
                if(row == null) {
                    sheet.createRow(startRow + j);
                }
            }
        }
    }


    /**
     * 找到需要插入的行数，并新建一个POI的row对象
     * @param sheet
     * @param rowIndex
     * @return
     */
    public static Row createRow(Sheet sheet, Integer rowIndex) {
        Row row = null;
        if (sheet != null && sheet.getRow(rowIndex) != null) {
            int lastRowNo = sheet.getLastRowNum();
            sheet.shiftRows(rowIndex, lastRowNo, 1);
        }
        row = sheet.createRow(rowIndex);
        return row;
    }


    /**
     * 删除一行
     * 往上覆盖一行
     * @param sheet
     * @param rowIndex
     */
    public static void removeRow(Sheet sheet,Integer rowIndex){
        removeRow(sheet,rowIndex,1);
    }

    /**
     * 【注意】：执行该方法前，最好确认被移除行下方的行数，行数越多，执行越慢。
     * 建议：在动态创建行数之前调用该方法，执行时间少。
     * 删除行，以下数据往上填填充。如：row的index为3，4两行需要删除，则rowIndex传入4，rowCount传入2即可
     * @param sheet             当前页sheet
     * @param rowIndex          当前行的index
     * @param rowCount          往上覆盖几行
     */
    public static void removeRow(Sheet sheet,Integer rowIndex,Integer rowCount){
        int abs = Math.abs(rowCount);
        int lastRowNum = sheet.getLastRowNum();
        if(lastRowNum < rowIndex+1){
            for(int i=0; i < abs; i++){
                Row row = sheet.getRow(lastRowNum - i);
                if(row!= null) {
                    sheet.removeRow(row);
                }
            }
            //sheet.shiftRows(sheet.getLastRowNum(), sheet.getLastRowNum(), -1 * abs);
        }else {
            sheet.shiftRows(rowIndex + 1, sheet.getLastRowNum(), -1 * abs);
        }
    }

    /**
     * 行复制功能        复制样式、合并单元格
     * @param fromRow
     * @param toRow
     */
    public static void copyRow(Row fromRow,Row toRow){
        toRow.setHeight(fromRow.getHeight());
        for(Iterator cellIt = fromRow.cellIterator(); cellIt.hasNext(); ) {
            Cell tmpCell = (Cell) cellIt.next();
            Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(tmpCell, newCell);
        }
    }

    public static String getCellValue(Cell cell) {
        DataFormatter formatter = new DataFormatter();
        return formatter.formatCellValue(cell);//直接获取到单元格的值
    }


    //poi设置自适应列宽
    public static void setColumnWidth(Sheet sheet) {
        //sheet的索引从0开始,获取sheet列数
        int maxColumn = sheet.getRow(0).getPhysicalNumberOfCells();
        /**第一种,实现自动换行，但不适用于中文
         for (int i = 0; i < maxColumn; i++) {
         sheet.autoSizeColumn(i);
         }
         */
        //第二种
        for (int columnNum = 0; columnNum <= maxColumn; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            // 遍历列的数据，获取这一列的最长字符串
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType().getCode()  == CellType.STRING.getCode()) {
                        //int length = currentCell.getStringCellValue().getBytes().length;
                        //上面是网上所有currentCell获取length答案，但试过后发现偏大，在一个文章下的评论区看到下面的计算,试了一下，列宽实现了自适应大小，还没搞懂原因
                        //length = (byte长度+string长度)/2，有了解的麻烦，麻烦留言解释一下，感谢！！
                        int length = ((currentCell.getStringCellValue().getBytes(StandardCharsets.UTF_8).length + currentCell.toString().length()) / 2) + 3;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            //将最长的length*256设为列宽
            // sheet.setColumnWidth((short)列数,(short)(length*256));
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }


    /**
     * 复制单元格
     * @param srcCell
     * @param distCell
     */
    public static void copyCell(Cell srcCell,Cell distCell) {
        Workbook wb = distCell.getSheet().getWorkbook();
        CellStyle toStyle = wb.createCellStyle();
        CellStyle fromStyle = srcCell.getCellStyle();

        //水平垂直对齐方式
        toStyle.setAlignment(fromStyle.getAlignment());
        toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
        //边框和边框颜色
        toStyle.setBorderBottom(fromStyle.getBorderBottom());
        toStyle.setBorderLeft(fromStyle.getBorderLeft());
        toStyle.setBorderRight(fromStyle.getBorderRight());
        toStyle.setBorderTop(fromStyle.getBorderTop());
        toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
        toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
        toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
        toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());
        //背景和前景
        if (fromStyle instanceof XSSFCellStyle) {
            XSSFCellStyle xssfToStyle = (XSSFCellStyle) toStyle;
            xssfToStyle.setFillBackgroundColor(((XSSFCellStyle) fromStyle).getFillBackgroundColorColor());
            xssfToStyle.setFillForegroundColor(((XSSFCellStyle) fromStyle).getFillForegroundColorColor());
        } else {
            toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
            toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());
        }
        toStyle.setDataFormat(fromStyle.getDataFormat());
        toStyle.setFillPattern(fromStyle.getFillPattern());
        if (fromStyle instanceof XSSFCellStyle) {
            toStyle.setFont(((XSSFCellStyle) fromStyle).getFont());
        } else if (fromStyle instanceof HSSFCellStyle) {
            toStyle.setFont(((HSSFCellStyle) fromStyle).getFont(wb));
        }
        toStyle.setHidden(fromStyle.getHidden());
        //首行缩进
        toStyle.setIndention(fromStyle.getIndention());
        toStyle.setLocked(fromStyle.getLocked());
        //旋转
        toStyle.setRotation(fromStyle.getRotation());
        toStyle.setWrapText(fromStyle.getWrapText());
        distCell.setCellStyle(toStyle);
    }

    /**
     * 所在列数字转字母
     *
     * @param columnIndex
     * @return
     */
    public static String excelColIndexToStr(int columnIndex) {
        if (columnIndex <= 0) {
            return null;
        }
        String columnStr = "";
        columnIndex--;
        do {
            if (columnStr.length() > 0) {
                columnIndex--;
            }
            columnStr = ((char) (columnIndex % 26 + (int) 'A')) + columnStr;
            columnIndex = (int) ((columnIndex - columnIndex % 26) / 26);
        } while (columnIndex > 0);
        return columnStr;
    }

}
